package com.adams.custom.page.core.service.impl;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.StringTokenizer;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;

import com.adams.access.db.bean.DicColumn;
import com.adams.access.db.bean.DicTable;
import com.adams.access.db.bean.ViewItem;
import com.adams.access.db.bean.ViewPanel;
import com.adams.core.exception.BusinessException;
import com.adams.custom.page.core.dto.DicColumnDto;
import com.adams.custom.page.core.service.DicTableService;
import com.adams.custom.page.core.service.SQLConstructServie;
import com.adams.custom.page.mgt.dao.impl.ViewItemDaoImpl;
import com.adams.custom.page.util.ViewConstant;
import com.adams.custom.page.util.ViewUtil;
import com.adams.utils.DateUtil;

/**
 * 
 * File Name : SQLConstructServieImpl.java
 * 
 * @Description : SQL语句组装实现类
 * @author 903903 陈加伟
 */

public class SQLConstructServieImpl implements SQLConstructServie
{
	Logger logger = Logger.getLogger(SQLConstructServieImpl.class.getName());
	/**
	 * Description : 页面信息项实体数据访问实现类 Author : 904370
	 */
	private ViewItemDaoImpl viewItemDaoImpl;

	/**
	 * Description : 数据字典服务接口 Author : 904370
	 */
	private DicTableService dicTableService;

	public void setViewItemDaoImpl(ViewItemDaoImpl viewItemDaoImpl)
	{
		this.viewItemDaoImpl = viewItemDaoImpl;
	}

	public void setDicTableService(DicTableService dicTableService)
	{
		this.dicTableService = dicTableService;
	}

	private static final String ROOTALIAS = "alias";

	private static final String SELECT_TABLE_ALIAS = "stalias_";

	public String ConstructSearchSQL(String tablecode,
			List<String> targetColumncodes,
			Map<String, Map<String, String[]>> conditionColumncodes,
			LinkedHashMap<String, String> orderColumncodes)
			throws BusinessException
	{
		logger.info("ConstructSearchSQL method run begin.");
		StringBuilder result = new StringBuilder();
		String orderBySQL = null;
		if (tablecode != null)
		{
			result.append(ConstructTargetSQL(tablecode, targetColumncodes));
			orderBySQL = ConstructOrderBySQL(tablecode, orderColumncodes);
			if (conditionColumncodes != null)
			{
				String sqlwhere = ConstructConditionSQL(tablecode,
						conditionColumncodes);
				if (!ViewUtil.isNuLLStr(sqlwhere))
				{
					result.append(" where ");
					result.append(sqlwhere);
					// StringBuilder sqlWhereSB = new StringBuilder(sqlwhere);
					// String rootalias = " " + ROOTALIAS + 0 + ".";
					// List<DicColumn> orderDicColumns = new
					// ArrayList<DicColumn>();
					// DicColumn orderDicColumn = null;
					// Set<String> orderColumncodeSet =
					// orderColumncodes.keySet();
					// for (String orderColumncode : orderColumncodeSet)
					// {
					// orderDicColumn = dicTableService.getDicColumn(
					// tablecode, orderColumncode);
					// orderDicColumns.add(orderDicColumn);
					// }
					// if (orderDicColumns.size() == 0)
					// {
					// DicTable dicTable = dicTableService
					// .getDicTable(tablecode);
					// List<String> keycolumnlist = dicTable
					// .getKeycolumnlist();
					// for (String keycolumn : keycolumnlist)
					// {
					// orderDicColumn = dicTableService.getDicColumn(
					// tablecode, keycolumn);
					// orderDicColumns.add(orderDicColumn);
					// }
					// }
					// int len = orderDicColumns.size();
					// for (int i = 0; i < len; i++)
					// {
					// orderDicColumn = orderDicColumns.get(i);
					// String key = rootalias
					// + orderDicColumn.getDicColumnPK()
					// .getColumncode() + " ";
					// int pos = sqlWhereSB.indexOf(key);
					// if (pos == -1)
					// {
					// sqlWhereSB.append(" and ");
					// sqlWhereSB.append(key);
					// String dataType = orderDicColumn.getDatatype();
					// if (DicColumn.DATATYPE_NUM.equals(dataType))
					// {
					// sqlWhereSB.append(" > 0 ");
					// } else if (DicColumn.DATATYPE_LOGIC
					// .equals(dataType))
					// {
					// break;
					// } else
					// {
					// sqlWhereSB.append(" > '' ");
					// }
					// }
					// }
					//
					// result.append(" where ");
					// result.append(sqlWhereSB.toString());
				}

			}
			result.append(orderBySQL);
		}
		logger.info("ConstructSearchSQL method run end.");
		return result.toString();
	}

	public String ConstructCountTargetSQL(String tablecode)
			throws BusinessException
	{
		logger.info("ConstructCountTargetSQL method run begin.");
		long aliasNumber = 0;
		String thisAlias = ROOTALIAS + aliasNumber;

		DicTable dicTable = dicTableService.getDicTable(tablecode);
		StringBuilder result = new StringBuilder("select count(");
		if (!dicTable.isCompositeKey())
		{
			result.append(thisAlias);
			result.append(".");
			result.append(dicTable.getKeycolumns());
		} else
		{
			result.append("*");
		}
		result.append(") from ");
		result.append(dicTableService.getTableNameSqlByTablecode(tablecode));
		result.append(" ");
		result.append(thisAlias);
		logger.info("ConstructCountTargetSQL method run end.");
		return result.toString();
	}

	/**
	 * Description : 根据目标panel组装查询目标语句
	 * 
	 * @param targetPanel
	 * @param alias
	 * @return
	 * @throws BusinessException
	 * 
	 */
	public String ConstructTargetSQL(ViewPanel targetPanel)
			throws BusinessException
	{
		logger.info("ConstructTargetSQL method run begin.");
		String tablecode = targetPanel.getTablecode();
		List<String> columncodes = new ArrayList<String>();

		List<ViewItem> viewItemsList = viewItemDaoImpl
				.findItemByViewPanelid(targetPanel.getId());
		for (ViewItem viewItem : viewItemsList)
		{
			columncodes.add(viewItem.getName());
		}

		String result = ConstructTargetSQL(tablecode, columncodes);
		logger.info("ConstructTargetSQL method run end.");
		return result;
	}

	public String ConstructTargetSQL(String tablecode,
			List<String> targetColumncodes) throws BusinessException
	{
		long aliasNumber = 0;
		String alias = ROOTALIAS + aliasNumber;

		StringBuilder result = new StringBuilder("select ");

		if (targetColumncodes == null || targetColumncodes.size() == 0)
		{
			logger.error("查询目标列未定义!");
			throw new BusinessException("select.column.isnull.exception");
		}

		int columnNum = 0;
		for (String columncode : targetColumncodes)
		{
			int pos = columncode.indexOf(ViewConstant.COLUMN_SEQ);
			String firstColumncode = columncode;
			if (pos > -1)
			{
				firstColumncode = columncode.substring(0, pos);
			}
			// 本表字段
			result.append(alias);
			result.append(".");
			result.append(firstColumncode);
			// 增加字段别名 减少冲突
			result.append(" column_" + (columnNum++));
			result.append(",");

			if (pos > 0)
			{
				String subselect = ConstructSubSelectSQL(tablecode, alias,
						columncode);
				if (!ViewUtil.isNuLLStr(subselect))
				{
					result.append(subselect);
					// 增加字段别名 减少冲突
					result.append(" column_" + (columnNum++));
					result.append(",");
				}
			}
		}

		result.deleteCharAt(result.length() - 1);
		result.append(" from ");
		result.append(dicTableService.getTableNameSqlByTablecode(tablecode));
		result.append(" ");
		result.append(alias);

		return result.toString();

	}

	/**
	 * Description : 构造查询select 中的子查询语句
	 * 
	 * @param tablecode
	 * @param alias
	 * @param columncode
	 * @return
	 * @throws BusinessException
	 * 
	 */
	@SuppressWarnings("unchecked")
	private String ConstructSubSelectSQL(String tablecode, String alias,
			String columncode) throws BusinessException
	{
		StringBuilder result = new StringBuilder();
		// 关系表字段列表
		List<String> refcolumncodes = ViewUtil.getListBySeq(columncode,
				ViewConstant.COLUMN_SEQ);

		// 产生表字段定义对象列表
		List<DicColumn> refDicColumns = new ArrayList<DicColumn>();
		// 查询实体字段关系字符串
		StringBuilder relcolumn = new StringBuilder();

		String targetTablecode = tablecode;

		// 用于保存循环运算结果 是否关系都在同一个数据
		// 如果相同就可以使用子查询提高查询性能,不相同就必须要使用多步跨数据库查询得到结果
		boolean notonedatabase = false;
		String targetDatabase = dicTableService.getDatabase(targetTablecode);

		String targetColumncode = "";
		int columncodesLen = refcolumncodes.size();

		for (int i = 0; i < columncodesLen; i++)
		{
			String refcolumncode = refcolumncodes.get(i);

			relcolumn.append(refcolumncode);
			relcolumn.append(",");

			targetColumncode = refcolumncode;
			String refDatabase = dicTableService.getDatabase(targetTablecode);
			if (!notonedatabase && !targetDatabase.equals(refDatabase))
			{
				notonedatabase = true;
			}

			DicColumn refDicColumn = dicTableService.getDicColumn(
					targetTablecode, refcolumncode);
			List<Map> enumerate = refDicColumn.getEnumerate();

			if (i == columncodesLen - 1)
			{
				continue;
			}
			if (enumerate.size() > 0)
			{
				if (i == columncodesLen - 2
						&& DicColumn.ENUMERATE_VALUE_TAG.equals(refcolumncodes
								.get(columncodesLen - 1)))
				{
					targetColumncode = refDicColumn.getDicColumnPK()
							.getColumncode();
					break;
				} else
				{
					throw new BusinessException("table.parse.exception");
				}
			} else
			{
				String sourcetable = refDicColumn.getSourcetable();
				if (ViewUtil.isNuLLStr(sourcetable))
				{
					throw new BusinessException("table.parse.exception");
				}
				DicColumn sourcePkDicColumn = dicTableService.getDicColumn(
						targetTablecode, refcolumncode);
				targetTablecode = sourcetable;
				refDicColumns.add(sourcePkDicColumn);
			}
		}
		// 没有关系表时，没有子查询语句
		if (refDicColumns.size() == 0)
		{
			return "";
		}

		if (relcolumn.length() > 0)
		{
			relcolumn.deleteCharAt(relcolumn.length() - 1);
		}

		// 如果本表有此冗余字段，直接使用此字段，减少关联表查询。提高查询性能
		List<DicColumn> rddDicColumns = dicTableService
				.findRddcolumnByTablecode(tablecode);
		for (DicColumn rddDicColumn : rddDicColumns)
		{
			String rddrelcolumn = rddDicColumn.getRddrelcolumn();
			if (relcolumn.toString().equals(rddrelcolumn))
			{
				result.append(alias);
				result.append(".");
				result.append(rddDicColumn.getDicColumnPK().getColumncode());
				return result.toString();
			}
		}

		// 如果字段关系是要跨数据库时,产生不了子查询语句
		if (notonedatabase)
		{
			return result.toString();
		}
		// 字段关系都在同一个数据库,使用子查询提高查询性能
		Collections.reverse(refDicColumns);

		int tableCodesLen = refDicColumns.size();

		int dbtnum = 0;

		result.append("(select ");
		result.append(SELECT_TABLE_ALIAS + dbtnum);
		result.append(".");
		result.append(targetColumncode);
		result.append(" from "
				+ dicTableService.getTableNameSqlByTablecode(targetTablecode));
		result.append(" " + SELECT_TABLE_ALIAS + dbtnum);
		result.append(" where 1=1 ");
		for (int i = 0; i < tableCodesLen; i++)
		{
			result.append(" and ");
			DicColumn refDicColumn = refDicColumns.get(i);
			DicColumn refTargetDicColumn = refDicColumn;
			DicColumn refSrcDicColumn = dicTableService
					.getSinglePkColumnByTablecode(refDicColumn.getSourcetable());
			result.append(SELECT_TABLE_ALIAS + dbtnum);
			result.append(".");
			result.append(refSrcDicColumn.getDicColumnPK().getColumncode());
			result.append(" = ");
			if (i == tableCodesLen - 1)
			{
				result.append(alias);
				result.append(".");
				result.append(refTargetDicColumn.getDicColumnPK()
						.getColumncode());
			} else
			{
				result.append("(select ");
				dbtnum++;
				result.append(SELECT_TABLE_ALIAS + dbtnum);
				result.append(".");
				result.append(refTargetDicColumn.getDicColumnPK()
						.getColumncode());
				result.append(" from ");
				result.append(dicTableService
						.getTableNameSqlByTablecode(refDicColumn
								.getDicColumnPK().getTablecode()));
				result.append(" " + SELECT_TABLE_ALIAS + dbtnum);
				result.append(" where 1=1");
			}
		}
		for (int i = 0; i < tableCodesLen - 1; i++)
		{
			result.append(")");
		}
		result.append(")");
		return result.toString();
	}

	/**
	 * Description : 根据表名 和过滤条件 组装查询条件语句
	 * 
	 * @param searchPanel
	 * @param inputMap
	 * @param alias
	 * @return
	 * @throws BusinessException
	 * 
	 */
	public String ConstructConditionSQL(String tablecode,
			Map<String, Map<String, String[]>> conditionColumncodes)
			throws BusinessException
	{
		StringBuilder result = new StringBuilder("1=1");

		Set<String> columncodes = conditionColumncodes.keySet();

		for (String columncode : columncodes)
		{
			long aliasNumber = 0;
			String alias = ROOTALIAS + aliasNumber;

			if (columncode.indexOf(ViewConstant.COLUMN_SEQ) == -1)
			{
				String compareConditionsSql = getCompareConditionsSql(alias,
						tablecode, columncode, conditionColumncodes
								.get(columncode));
				if (!ViewUtil.isNuLLStr(compareConditionsSql))
				{
					result.append(compareConditionsSql);
				}
				continue;
			}

			String associateTableCode = tablecode;
			// 查询字段是否在本表中有冗余字段
			boolean hasRddDicColumn = false;
			DicColumn rddDicColumn = null;
			List<DicColumn> rddDicColumns = dicTableService
					.findRddcolumnByTablecode(associateTableCode);
			for (DicColumn dicColumn : rddDicColumns)
			{
				if (columncode.equals(dicColumn.getRddrelcolumn()))
				{
					hasRddDicColumn = true;
					rddDicColumn = dicColumn;
					break;
				}
			}
			if (hasRddDicColumn)
			{
				// 本表中有冗余字段的情况下，直接使用冗余字段进行查询，提高性能
				// 使用冗余字段查询 begin
				String compareConditionsSql = getCompareConditionsSql(alias,
						tablecode, rddDicColumn.getDicColumnPK()
								.getColumncode(), conditionColumncodes
								.get(columncode));
				if (!ViewUtil.isNuLLStr(compareConditionsSql))
				{
					result.append(compareConditionsSql);
				}
				continue;
				// 使用冗余字段查询 end
			}

			// 本表中没有冗余字段的情况下，进行多表联合查询 begin
			String refSelect = getRefSelect(tablecode, columncode,
					conditionColumncodes);
			if (!ViewUtil.isNuLLStr(refSelect))
			{
				result.append(refSelect);
			}
			// 本表中没有冗余字段的情况下，进行多表联合查询 end
		}

		return result.toString();
	}

	/**
	 * Description : 处理列关系字符,根据数据库进行分组
	 * 
	 * @param tablecode
	 * @param columncodes
	 * @return
	 * @throws BusinessException
	 * 
	 */
	private List<List<DicColumn>> getItemcolumnsByColumncodes(String tablecode,
			String columncodes) throws BusinessException
	{
		List<List<DicColumn>> result = new ArrayList<List<DicColumn>>();
		List<DicColumn> dicColumns = new ArrayList<DicColumn>();
		StringTokenizer st = new StringTokenizer(columncodes,
				ViewConstant.COLUMN_SEQ);
		String database = dicTableService.getDatabase(tablecode);
		String reftablecode = tablecode;
		while (st.hasMoreTokens())
		{
			String column = st.nextToken();
			if (!ViewUtil.isNuLLStr(column))
			{
				if (ViewUtil.isNuLLStr(reftablecode))
				{
					throw new BusinessException("view.config.exception");
				}
				String tempdatabase = dicTableService.getDatabase(reftablecode);
				DicColumn dicColumn = dicTableService.getDicColumn(
						reftablecode, column);
				if (database.equals(tempdatabase))
				{
					dicColumns.add(dicColumn);
				} else
				{
					result.add(dicColumns);
					dicColumns = new ArrayList<DicColumn>();
					dicColumns.add(dicColumn);
					database = tempdatabase;
				}
				if (dicColumn.getTargettable() != null)
				{
					reftablecode = dicColumn.getTargettable();
				} else
				{
					reftablecode = dicColumn.getSourcetable();
				}
			}
		}
		result.add(dicColumns);
		return result;
	}

	@SuppressWarnings("unchecked")
	private String getCompareConditionByOtherDataBase(
			List<List<DicColumn>> itemcolumns, String columncodes,
			Map<String, Map<String, String[]>> conditionColumncodes)
			throws BusinessException
	{
		StringBuilder result = new StringBuilder();
		List<DicColumn> dicColumns = null;
		DicColumn dicColumn = null;
		int itemLen = itemcolumns.size();
		int columnLen = 0;
		if (itemLen > 1)
		{
			// 先查询跨数据库的结果集用于本表查询
			for (int i = itemLen - 1; i > 1; i--)
			{
				int aliasNumber = 0;
				String alias = ROOTALIAS + aliasNumber;
				dicColumns = itemcolumns.get(i);
				columnLen = dicColumns.size();
				StringBuilder sql = new StringBuilder();
				for (int j = 0; j < columnLen; j++)
				{
					dicColumn = dicColumns.get(j);
					String associateTableCode = dicColumn.getDicColumnPK()
							.getTablecode();
					String columncode = dicColumn.getDicColumnPK()
							.getColumncode();
					DicTable dicTable = dicTableService
							.getDicTable(associateTableCode);
					if (dicTable.isCompositeKey())
					{
						logger.error("配置错误，" + associateTableCode + "表不是单主键表！");
						throw new BusinessException("table.config.exception");
					}
					if (j == 0)
					{
						sql.append("select ");
						sql.append(alias + ".");
						sql.append(dicTable.getKeycolumns());
						sql.append(" from ");
						sql.append(associateTableCode);
						sql.append(" " + alias);
						sql.append(" where 1=1 ");
					}
					// 最后一条
					if (j == columnLen - 1)
					{

						if (i == itemLen - 1)
						{
							// 如果是分组中的最后一组,使用传入参数作查询过滤条件
							String compareConditionsSql = getCompareConditionsSql(
									alias, associateTableCode, columncode,
									conditionColumncodes.get(columncodes));
							if (!ViewUtil.isNuLLStr(compareConditionsSql))
							{
								sql.append(compareConditionsSql);
							}
						} else
						{
							// 非分组中的最后一组,使用之前分组的查询结果作查询过滤条件
							if (result.length() > 0)
							{
								sql.append(alias);
								sql.append(".");
								sql.append(columncode);
								sql.append(result);
							}
						}

						for (int k = 1; k < j; k++)
						{
							sql.append(")");
						}
						dicColumn = dicColumns.get(0);
						String selectTablecode = dicColumn.getDicColumnPK()
								.getTablecode();
						DicColumn keyDicColumn = dicTableService
								.getSinglePkColumnByTablecode(selectTablecode);
						String keycolumncode = keyDicColumn.getDicColumnPK()
								.getColumncode();
						String datatype = keyDicColumn.getDatatype();
						List<DicColumnDto> dicColumnDtos = new ArrayList<DicColumnDto>();
						DicColumnDto dicColumnDto = new DicColumnDto(
								keyDicColumn);
						dicColumnDtos.add(dicColumnDto);
						List<Map> idList = dicTableService.findData(
								selectTablecode, dicColumnDtos, sql.toString(),
								0, 0);

						result.delete(0, result.length());
						int idslen = idList.size();
						if (idslen > 0)
						{
							if (idslen == 1)
							{
								result.append("=");
								Map id = idList.get(0);
								String value = id.get(keycolumncode).toString();
								if (DicColumn.DATATYPE_NUM.equals(datatype))
								{
									result.append(value);
								} else
								{
									result.append("'");
									result.append(value);
									result.append("' ");
								}
							} else
							{
								result.append(" in (");
								for (Map id : idList)
								{
									String value = id.get(keycolumncode)
											.toString();
									if (DicColumn.DATATYPE_NUM.equals(datatype))
									{
										result.append(value);
									} else
									{
										result.append("'");
										result.append(value);
										result.append("'");
										result.append(",");
									}
								}
								result.deleteCharAt(result.length() - 1);
								result.append(") ");
							}

						}
						break;
					}

					if (dicColumn.getTargettable() != null)
					{
						if (!isKeyColumn(dicColumn))
						{
							logger.error(associateTableCode + "表" + columncode
									+ "字段不是主键,不能设置Targettable！");
							throw new BusinessException("table.parse.exception");
						}
						String targetTablecode = dicColumn.getTargettable();
						List<DicColumn> associateDicColumns = dicTableService
								.findDBDicColumnsByTablecode(targetTablecode);

						for (DicColumn associateDicColumn : associateDicColumns)
						{
							if (associateTableCode.equals(associateDicColumn
									.getSourcetable()))
							{
								sql.append(ConstructConditionAssociateTable(
										columncode, targetTablecode,
										associateDicColumn.getDicColumnPK()
												.getColumncode(), alias,
										++aliasNumber));
								alias = ROOTALIAS + aliasNumber;
								break;
							}
						}
					} else
					{
						String sourcetable = dicColumn.getSourcetable();
						if (sourcetable == null)
						{
							logger.error(associateTableCode + "表" + columncode
									+ "字段的关联表不存在！");
							throw new BusinessException("table.parse.exception");
						}
						DicTable sourceDicTable = dicTableService
								.getDicTable(sourcetable);
						sql.append(ConstructConditionAssociateTable(dicColumn
								.getDicColumnPK().getColumncode(), sourcetable,
								sourceDicTable.getKeycolumns(), alias,
								++aliasNumber));
						alias = ROOTALIAS + aliasNumber;
					}
				}
			}

			return result.toString();
		}
		return result.toString();
	}

	/**
	 * Description : 根据列关系产生查询过程条件
	 * 
	 * @param tablecode
	 * @param columncodes
	 * @param conditionColumncodes
	 * @return
	 * @throws BusinessException
	 * 
	 */
	private String getRefSelect(String tablecode, String columncodes,
			Map<String, Map<String, String[]>> conditionColumncodes)
			throws BusinessException
	{
		StringBuilder result = new StringBuilder("");
		List<List<DicColumn>> itemcolumns = getItemcolumnsByColumncodes(
				tablecode, columncodes);

		int itemLen = itemcolumns.size();
		int aliasNumber = 0;
		String alias = ROOTALIAS + aliasNumber;
		String compareCondition = getCompareConditionByOtherDataBase(
				itemcolumns, columncodes, conditionColumncodes);

		List<DicColumn> dicColumns = itemcolumns.get(0);
		int columnLen = dicColumns.size();
		for (int j = 0; j < columnLen; j++)
		{
			DicColumn dicColumn = dicColumns.get(j);
			String associateTableCode = dicColumn.getDicColumnPK()
					.getTablecode();
			String columncode = dicColumn.getDicColumnPK().getColumncode();
			if (j == columnLen - 1)
			{
				String compareConditionsSql = "";
				if (itemLen == 1)
				{
					compareConditionsSql = getCompareConditionsSql(alias,
							associateTableCode, columncode,
							conditionColumncodes.get(columncodes));
				} else if (itemLen > 1 && !ViewUtil.isNuLLStr(compareCondition))
				{
					compareConditionsSql = getCompareConditionsSql(alias,
							associateTableCode, columncode, compareCondition);
				}

				if (!ViewUtil.isNuLLStr(compareConditionsSql))
				{
					result.append(compareConditionsSql);
				}
				for (int k = 0; k < j; k++)
				{
					result.append(")");
				}
				break;
			}

			if (dicColumn.getTargettable() != null)
			{
				if (!isKeyColumn(dicColumn))
				{
					logger.error(associateTableCode + "表" + columncode
							+ "字段不是主键,不能设置Targettable！");
					throw new BusinessException("table.parse.exception");
				}
				String targetTablecode = dicColumn.getTargettable();
				List<DicColumn> associateDicColumns = dicTableService
						.findDBDicColumnsByTablecode(targetTablecode);

				for (DicColumn associateDicColumn : associateDicColumns)
				{
					if (associateTableCode.equals(associateDicColumn
							.getSourcetable()))
					{
						result.append(ConstructConditionAssociateTable(
								columncode, targetTablecode, associateDicColumn
										.getDicColumnPK().getColumncode(),
								alias, ++aliasNumber));
						alias = ROOTALIAS + aliasNumber;
						break;
					}
				}
			} else
			{
				String sourcetable = dicColumn.getSourcetable();
				if (sourcetable == null)
				{
					logger.error(associateTableCode + "表" + columncode
							+ "字段的关联表不存在！");
					throw new BusinessException("table.parse.exception");
				}
				DicTable sourceDicTable = dicTableService
						.getDicTable(sourcetable);
				result.append(ConstructConditionAssociateTable(dicColumn
						.getDicColumnPK().getColumncode(), sourcetable,
						sourceDicTable.getKeycolumns(), alias, ++aliasNumber));
				alias = ROOTALIAS + aliasNumber;
			}

		}
		return result.toString();
	}

	/**
	 * Description : 根据表别名 本表字段 及比较条件 得到对此字段的过滤条件
	 * 
	 * @param alias
	 * @param columncode
	 * @param conditionMap
	 * @return
	 * @throws BusinessException
	 * 
	 */
	private String getCompareConditionsSql(String alias, String tablecode,
			String columncode, Map<String, String[]> conditionMap)
			throws BusinessException
	{
		StringBuilder result = new StringBuilder();
		Set<String> compareTypes = conditionMap.keySet();
		for (String compareType : compareTypes)
		{
			String[] parameterValues = conditionMap.get(compareType);

			DicColumn dicColumn = dicTableService.getDicColumn(tablecode,
					columncode);
			List<StringBuilder> compareConditions = getCompareCondition(
					dicColumn, parameterValues, compareType);
			for (StringBuilder compareCondition : compareConditions)
			{
				result.append(" and ");
				result.append(alias);
				result.append(".");
				result.append(columncode);
				result.append(" ");
				result.append(compareCondition);
			}
		}
		return result.toString();
	}

	/**
	 * Description : 根据表别名 本表字段 及比较条件 得到对此字段的过滤条件
	 * 
	 * @param alias
	 * @param columncode
	 * @param conditionMap
	 * @return
	 * @throws BusinessException
	 * 
	 */
	private String getCompareConditionsSql(String alias, String tablecode,
			String columncode, String compareCondition)
			throws BusinessException
	{
		StringBuilder result = new StringBuilder();
		result.append(" and ");
		result.append(alias);
		result.append(".");
		result.append(columncode);
		result.append(" ");
		result.append(compareCondition);
		return result.toString();
	}

	@SuppressWarnings("unchecked")
	public String ConstructConditionSQL(ViewPanel searchPanel,
			ViewPanel gridPanel, Map inputMap) throws BusinessException
	{
		logger.info("ConstructConditionSQL method run begin.");
		StringBuilder result = new StringBuilder(" where 1=1");
		// 查询条过滤条件
		String sqlBySearchPanel = ConstructConditionSQLBySearchPanel(
				searchPanel, inputMap);
		if (!ViewUtil.isNuLLStr(sqlBySearchPanel))
		{
			result.append(" and ");
			result.append(sqlBySearchPanel);
		}
		// 列表filter过滤条件
		String sqlByGridPanelFilter = ConstructConditionSQLByGridPanelFilter(
				gridPanel, inputMap);
		if (!ViewUtil.isNuLLStr(sqlByGridPanelFilter))
		{
			result.append(" and ");
			result.append(sqlByGridPanelFilter);
		}
		// 列表子父级关系过滤条件
		String sqlByGridPanelParentTable = ConstructConditionSQLByGridPanelRelationParam(
				gridPanel, inputMap);
		if (!ViewUtil.isNuLLStr(sqlByGridPanelParentTable))
		{
			result.append(" and ");
			result.append(sqlByGridPanelParentTable);
		}

		// 性能优化 经测试发现并没有对性能得到提高反而下降 以此证明网上说的这种性能忧化并不真实
		// 当order by 子句要用到的字段 如果不包含在where 子句中时,不能使用索引
		// 所以在where 子句中判断是否包含order by的字段,如果已经包含不处理,如果不包含就加上简单的对字段的过滤
		// String rootalias = " " + ROOTALIAS + 0 + ".";
		// String tablecode = gridPanel.getTablecode();
		// List<DicColumn> orderColumncodes = new ArrayList<DicColumn>();
		// DicColumn orderDicColumn = null;
		// List<ViewItem> viewItemsList = viewItemDaoImpl
		// .findItemByViewPanelid(gridPanel.getId());
		// for (ViewItem viewItem : viewItemsList)
		// {
		// if (viewItem.getOrderFlag())
		// {
		// orderDicColumn = dicTableService.getDicColumn(tablecode,
		// viewItem.getFirstColumncode());
		// orderColumncodes.add(orderDicColumn);
		// }
		// }
		// if (orderColumncodes.size() == 0)
		// {
		// DicTable dicTable = dicTableService.getDicTable(tablecode);
		// List<String> keycolumnlist = dicTable.getKeycolumnlist();
		// for (String keycolumn : keycolumnlist)
		// {
		// orderDicColumn = dicTableService.getDicColumn(tablecode,
		// keycolumn);
		// orderColumncodes.add(orderDicColumn);
		// }
		// }
		// int len = orderColumncodes.size();
		// for (int i = 0; i < len; i++)
		// {
		// orderDicColumn = orderColumncodes.get(i);
		// String key = rootalias
		// + orderDicColumn.getDicColumnPK().getColumncode() + " ";
		// int pos = result.indexOf(key);
		// if (pos == -1)
		// {
		// result.append(" and ");
		// result.append(key);
		// String dataType = orderDicColumn.getDatatype();
		// if (DicColumn.DATATYPE_NUM.equals(dataType))
		// {
		// result.append(" > 0 ");
		// } else if (DicColumn.DATATYPE_LOGIC.equals(dataType))
		// {
		// break;
		// } else
		// {
		// result.append(" > '' ");
		// }
		// }
		// }
		logger.info("ConstructConditionSQL method run end.");
		return result.toString();
	}

	public final static Map<String, String> GRID_FILTER_MAP = new HashMap<String, String>();
	static
	{
		GRID_FILTER_MAP.put(SQLConstructServie.keyWord_like,
				SQLConstructServie.keyWord_like);
		GRID_FILTER_MAP.put("gt", SQLConstructServie.keyWord_greater);
		GRID_FILTER_MAP.put("lt", SQLConstructServie.keyWord_less);
		GRID_FILTER_MAP.put("eq", SQLConstructServie.keyWord_equal);
		GRID_FILTER_MAP.put("neq", SQLConstructServie.keyWord_notEqual);
		GRID_FILTER_MAP.put(SQLConstructServie.keyWord_in,
				SQLConstructServie.keyWord_in);
	}

	/**
	 * Description : 从页面传过来的列表filter处理
	 * 
	 * @param gridPanel
	 * @param inputMap
	 * @return
	 * @throws BusinessException
	 * 
	 */
	@SuppressWarnings("unchecked")
	private String ConstructConditionSQLByGridPanelFilter(ViewPanel gridPanel,
			Map inputMap) throws BusinessException
	{
		if (gridPanel == null || gridPanel.getId() == 0)
		{
			return "";
		}
		String tablecode = gridPanel.getTablecode();

		List<ViewItem> viewItemsList = orderViewItemsList(viewItemDaoImpl
				.findItemByViewPanelid(gridPanel.getId()));
		Map<String, Map<String, String[]>> conditionColumncodes = new HashMap<String, Map<String, String[]>>();
		Set<String> viewItemColumn = new HashSet<String>();

		for (ViewItem viewItem : viewItemsList)
		{
			String columncode = viewItem.getName();
			if (!viewItem.isMultilevelByName())
			{
				viewItemColumn.add(columncode);
			}
			DicColumn finalDicColumn = dicTableService.getFinalDicColumn(
					tablecode, viewItem.getColumncodes());
			String finalcolumncode = finalDicColumn.getDicColumnPK()
					.getColumncode();
			int pos = columncode.lastIndexOf(finalcolumncode);
			if (pos == -1)
			{
				continue;
			}
			int len = pos + finalcolumncode.length();
			if (len < columncode.length())
			{
				columncode = columncode.substring(0, len);
			}

			String viewItemName = viewItem.getViewNameNoEnumerateTag();

			ConstructConditionSQLByGridPanelFilter(inputMap,
					conditionColumncodes, columncode, viewItemName);
		}

		List<DicColumn> dicColumns = dicTableService
				.findDBDicColumnsByTablecode(tablecode);
		for (DicColumn dicColumn : dicColumns)
		{
			String columncode = dicColumn.getDicColumnPK().getColumncode();
			if (viewItemColumn.contains(columncode))
			{
				continue;
			}
			ConstructConditionSQLByGridPanelFilter(inputMap,
					conditionColumncodes, columncode, columncode);
		}

		String result = ConstructConditionSQL(tablecode, conditionColumncodes);
		return result;
	}

	@SuppressWarnings("unchecked")
	private void ConstructConditionSQLByGridPanelFilter(Map inputMap,
			Map<String, Map<String, String[]>> conditionColumncodes,
			String columncode, String viewItemName)
	{
		Set<String> filterKeySet = GRID_FILTER_MAP.keySet();
		for (String filterKey : filterKeySet)
		{
			String filterParam = ViewConstant.JSON_GRID_FILTER + viewItemName
					+ "_" + filterKey;
			if (!ViewUtil.isNuLLValueInMap(inputMap, filterParam))
			{
				Map<String, String[]> compareMap = null;
				if (!ViewUtil
						.isNuLLValueInMap(conditionColumncodes, columncode))
				{
					compareMap = conditionColumncodes.get(columncode);
				} else
				{
					compareMap = new HashMap<String, String[]>();
				}
				String inputValue = (String) inputMap.get(filterParam);
				String[] values = null;
				if (keyWord_in.equals(filterKey))
				{
					values = inputValue.split(",");
				} else
				{
					values = new String[1];
					values[0] = inputValue;
				}
				compareMap.put(GRID_FILTER_MAP.get(filterKey), values);
				conditionColumncodes.put(columncode, compareMap);
			}
		}
	}

	/**
	 * Description : 列表记录根据 父子参数 过滤 处理
	 * 
	 * @param gridPanel
	 * @param inputMap
	 * @return
	 * @throws BusinessException
	 * 
	 */
	@SuppressWarnings("unchecked")
	private String ConstructConditionSQLByGridPanelRelationParam(
			ViewPanel gridPanel, Map inputMap) throws BusinessException
	{
		if (gridPanel == null || gridPanel.getId() == 0)
		{
			return "";
		}
		if (ViewUtil.isNuLLValueInMap(inputMap,
				ViewConstant.JSON_GRID_RELATION_PARAM))
		{
			return "";
		}
		Map relationParam = (Map) inputMap
				.get(ViewConstant.JSON_GRID_RELATION_PARAM);
		String range = (String) relationParam
				.get(ViewConstant.JSON_GRID_RELATION_PARAM_RANGE);
		String relation = (String) relationParam
				.get(ViewConstant.JSON_GRID_RELATION_PARAM_RELATION);
		String lastcolumnvalue = (String) relationParam
				.get(ViewConstant.JSON_GRID_RELATION_PARAM_LCV);
		List<String> tablecodes = new ArrayList<String>();
		List<String> columncodes = new ArrayList<String>();
		StringTokenizer st = new StringTokenizer(relation,
				ViewConstant.COLUMN_SEQ);
		while (st.hasMoreTokens())
		{
			String tableorcolumn = st.nextToken();
			if (tablecodes.size() == columncodes.size())
			{
				tablecodes.add(tableorcolumn);
			} else
			{
				columncodes.add(tableorcolumn);
			}
		}
		int tablelen = tablecodes.size();
		if (columncodes.size() != tablelen)
		{
			logger.error("关系参数出错,输入的关系结构为:" + relation);
			throw new BusinessException("data.input.exception");
		}

		StringBuilder result = new StringBuilder();
		long aliasNumber = 0;
		if (tablelen == 0)
		{
			return "";
		} else if (tablelen == 1)
		{
			result.append(ROOTALIAS + aliasNumber);
			result.append(".");
			result.append(columncodes.get(0));
			result.append(" ");
			DicColumn dicColumn = dicTableService.getDicColumn(tablecodes
					.get(0), columncodes.get(0));
			String[] parameterValues = new String[1];
			parameterValues[0] = lastcolumnvalue;
			List<StringBuilder> compareConditions = null;
			if (ViewConstant.JSON_GRID_RELATION_PARAM_RANGE_INCLUDE
					.equals(range))
			{
				compareConditions = getCompareCondition(dicColumn,
						parameterValues, keyWord_equal);
			} else
			{
				compareConditions = getCompareCondition(dicColumn,
						parameterValues, keyWord_notEqual);
			}
			result.append(compareConditions.get(0));
			result.append(" ");
		} else if (tablelen == 3)
		{
			String table1 = tablecodes.get(1);
			String table2 = tablecodes.get(2);
			String column0 = columncodes.get(0);
			String column1 = columncodes.get(1);
			String column2 = columncodes.get(2);
			// 判断是否在同一个数据库
			boolean issamedb = dicTableService.isSameDatabase(tablecodes);
			if (!issamedb)
			{
				result.append(ROOTALIAS + aliasNumber);
				result.append(".");
				result.append(column0);
				result.append(" ");
				if (ViewConstant.JSON_GRID_RELATION_PARAM_RANGE_EXCLUDE
						.equals(range))
				{
					result.append("not ");
				}
				result.append("in (");
				StringBuilder sql = new StringBuilder();
				sql.append("select ");
				sql.append(column1);
				sql.append(" from ");
				sql.append(table1);
				sql.append(" where ");
				sql.append(column2);
				sql.append(" ");
				DicColumn dicColumn = dicTableService.getDicColumn(table2,
						column2);
				String[] parameterValues = new String[1];
				parameterValues[0] = lastcolumnvalue;
				List<StringBuilder> compareConditions = getCompareCondition(
						dicColumn, parameterValues, keyWord_equal);
				sql.append(compareConditions.get(0));

				DicColumn selectDicColumn = dicTableService.getDicColumn(
						table1, column1);
				String keycolumncode = selectDicColumn.getDicColumnPK()
						.getColumncode();
				String datatype = selectDicColumn.getDatatype();
				List<DicColumnDto> dicColumnDtos = new ArrayList<DicColumnDto>();
				DicColumnDto dicColumnDto = new DicColumnDto(selectDicColumn);
				dicColumnDtos.add(dicColumnDto);
				List<Map> idList = dicTableService.findData(table1,
						dicColumnDtos, sql.toString(), 0, 0);
				if (idList.size() > 0)
				{
					for (Map id : idList)
					{
						String value = id.get(keycolumncode).toString();
						if (DicColumn.DATATYPE_NUM.equals(datatype))
						{
							result.append(value);
						} else
						{
							result.append("'");
							result.append(value);
							result.append("' ");
						}
						result.append(",");
					}
					result.deleteCharAt(result.length() - 1);
				}
				result.append(") ");
			} else
			{
				if (ViewConstant.JSON_GRID_RELATION_PARAM_RANGE_EXCLUDE
						.equals(range))
				{
					result.append("not ");
				}

				DicTable dicTable1 = dicTableService.getDicTable(table1);
				List<String> secondKeyColumns = dicTable1.getKeycolumnlist();
				aliasNumber++;

				result.append("exists(select ");
				if (secondKeyColumns.size() > 0)
				{
					for (String secondKeyColumn : secondKeyColumns)
					{
						result.append(ROOTALIAS + aliasNumber);
						result.append(".");
						result.append(secondKeyColumn);
						result.append(",");
					}
					result.deleteCharAt(result.length() - 1);
				} else
				{
					result.append("*");
				}
				result.append(" from ");
				result.append(table1);
				result.append(" ");
				result.append(ROOTALIAS + aliasNumber);
				result.append(" where ");
				result.append(ROOTALIAS + aliasNumber);
				result.append(".");
				result.append(column1);
				result.append("=");
				result.append(ROOTALIAS);
				result.append(aliasNumber - 1);
				result.append(".");
				result.append(column0);
				result.append(" and ");
				result.append(ROOTALIAS + aliasNumber);
				result.append(".");
				result.append(column2);
				result.append(" ");
				DicColumn dicColumn = dicTableService.getDicColumn(table2,
						column2);
				String[] parameterValues = new String[1];
				parameterValues[0] = lastcolumnvalue;
				List<StringBuilder> compareConditions = getCompareCondition(
						dicColumn, parameterValues, keyWord_equal);
				result.append(compareConditions.get(0));
				result.append(" ");
				result.append(") ");
			}
		} else
		{
			logger.error("关系参数出错,输入的关系结构为:" + relation);
			throw new BusinessException("data.input.exception");

		}
		return result.toString();
	}

	@SuppressWarnings("unchecked")
	private String ConstructConditionSQLBySearchPanel(ViewPanel searchPanel,
			Map inputMap) throws BusinessException
	{
		if (searchPanel == null || searchPanel.getId() == 0)
		{
			return "1=1";
		}
		String tablecode = searchPanel.getTablecode();
		List<ViewItem> viewItemsList = orderViewItemsList(viewItemDaoImpl
				.findItemByViewPanelid(searchPanel.getId()));

		LinkedHashMap<String, Map<String, String[]>> conditionColumncodes = new LinkedHashMap<String, Map<String, String[]>>();
		for (ViewItem viewItem : viewItemsList)
		{
			String columncode = viewItem.getName();

			Map<String, String[]> columncodeCompareMap = null;
			if (conditionColumncodes.containsKey(columncode))
			{
				columncodeCompareMap = conditionColumncodes.get(columncode);
			} else
			{
				columncodeCompareMap = new HashMap<String, String[]>();
			}
			String xtype = viewItem.getXtype();

			String viewName = viewItem.getViewName();
			String[] parameterValues = null;
			String compareType = null;

			// 产生对比数值
			if (equalXtypeSet.contains(xtype))
			{
				compareType = keyWord_equal;
				if (ViewUtil.isNuLLValueInMap(inputMap, viewName))
				{
					continue;
				}
				String parameterValue = inputMap.get(viewName).toString();
				if (ViewUtil.isNuLLStr(parameterValue))
				{
					continue;
				}
				parameterValues = new String[1];
				parameterValues[0] = parameterValue;
				columncodeCompareMap.put(compareType, parameterValues);
			} else if (ViewItem.XTYPE_2.equals(xtype))
			{
				compareType = keyWord_like;
				if (ViewUtil.isNuLLValueInMap(inputMap, viewName))
				{
					continue;
				}
				String parameterValue = inputMap.get(viewName).toString();
				if (ViewUtil.isNuLLStr(parameterValue))
				{
					continue;
				}
				parameterValues = new String[1];
				parameterValues[0] = parameterValue;
				columncodeCompareMap.put(compareType, parameterValues);
			} else if (ViewItem.XTYPE_3.equals(xtype))
			{
				List<String> colunmcodes = viewItem.getColumncodes();
				DicColumn finalDicColumn = dicTableService.getFinalDicColumn(
						tablecode, colunmcodes);
				String datatype = finalDicColumn.getDatatype();
				compareType = keyWord_between;
				if (ViewUtil.isNuLLValueInMap(inputMap, viewName))
				{
					continue;
				}
				String parameterValue = inputMap.get(viewName).toString();
				if (ViewUtil.isNuLLStr(parameterValue))
				{
					continue;
				}
				parameterValues = new String[2];
				if (DicColumn.DATATYPE_TIME.equals(datatype))
				{
					parameterValues[0] = parameterValue + " 00:00:00";
					parameterValues[1] = parameterValue + " 23:59:59";
				} else
				{
					parameterValues[0] = parameterValue;
					parameterValues[1] = parameterValue;
				}
				columncodeCompareMap.put(compareType, parameterValues);
			} else if (ViewItem.XTYPE_4.equals(xtype))
			{
				List<String> colunmcodes = viewItem.getColumncodes();
				DicColumn finalDicColumn = dicTableService.getFinalDicColumn(
						tablecode, colunmcodes);
				String datatype = finalDicColumn.getDatatype();

				String beforeViewName = ViewConstant.JSON_DATE_BEFORE
						+ viewName;
				String parameterValue0 = "";
				if (!ViewUtil.isNuLLValueInMap(inputMap, beforeViewName))
				{
					parameterValue0 = inputMap.get(beforeViewName).toString();
					if (!ViewUtil.isNuLLStr(parameterValue0))
					{
						compareType = keyWord_notLess;
						parameterValues = new String[1];
						if (DicColumn.DATATYPE_TIME.equals(datatype))
						{
							parameterValues[0] = parameterValue0 + " 00:00:00";
						} else
						{
							parameterValues[0] = parameterValue0;
						}
						columncodeCompareMap.put(compareType, parameterValues);
					}
				}

				String afterViewName = ViewConstant.JSON_DATE_AFTER + viewName;
				String parameterValue1 = "";
				if (!ViewUtil.isNuLLValueInMap(inputMap, afterViewName))
				{
					parameterValue1 = inputMap.get(afterViewName).toString();
					if (!ViewUtil.isNuLLStr(parameterValue1))
					{
						compareType = keyWord_notGreater;
						parameterValues = new String[1];
						if (DicColumn.DATATYPE_TIME.equals(datatype))
						{
							parameterValues[0] = parameterValue1 + " 23:59:59";
						} else
						{
							parameterValues[0] = parameterValue1;
						}
						columncodeCompareMap.put(compareType, parameterValues);
					}
				}

				if (!ViewUtil.isNuLLStr(parameterValue0)
						&& !ViewUtil.isNuLLStr(parameterValue1))
				{
					if ((parameterValue0.compareTo(parameterValue1)) > 0)
					{
						throw new BusinessException(
								"data.input.betweendate.exception");
					}
				}
			} else if (ViewItem.XTYPE_6.equals(xtype))
			{
				compareType = keyWord_in;
				if (ViewUtil.isNuLLValueInMap(inputMap, viewName))
				{
					continue;
				}
				String parameterValue = inputMap.get(viewName).toString();
				if (ViewUtil.isNuLLStr(parameterValue))
				{
					continue;
				}
				parameterValues = parameterValue.split(",");
				columncodeCompareMap.put(compareType, parameterValues);
			}

			if (columncodeCompareMap.size() > 0)
			{
				conditionColumncodes.put(columncode, columncodeCompareMap);
			}
		}
		if (conditionColumncodes.size() == 0)
		{
			return "1=1";
		}

		String sqlwhere = ConstructConditionSQL(tablecode, conditionColumncodes);
		return sqlwhere;
	}

	private List<StringBuilder> getCompareCondition(DicColumn dicColumn,
			String[] parameterValues, String compareType)
			throws BusinessException
	{
		List<StringBuilder> result = new ArrayList<StringBuilder>();

		if (keyWord_between.equals(compareType))
		{
			StringBuilder compareCondition1 = new StringBuilder();
			compareCondition1.append(keyWord_notLess);
			if (DicColumn.STR_DATATYPE_SET.contains(dicColumn.getDatatype()))
			{
				compareCondition1.append("'");
				String nowDateStr = dealSysFunction(dicColumn.getDatatype(),
						parameterValues[0]);
				compareCondition1.append(nowDateStr);
				compareCondition1.append("'");
			} else
			{
				compareCondition1.append(parameterValues[0]);
			}

			result.add(compareCondition1);

			StringBuilder compareCondition2 = new StringBuilder();
			compareCondition2.append(keyWord_notGreater);
			if (DicColumn.STR_DATATYPE_SET.contains(dicColumn.getDatatype()))
			{
				compareCondition2.append("'");
				String nowDateStr = dealSysFunction(dicColumn.getDatatype(),
						parameterValues[1]);
				compareCondition2.append(nowDateStr);
				compareCondition2.append("'");
			} else
			{
				compareCondition2.append(parameterValues[1]);
			}
			result.add(compareCondition2);
		} else if (keyWord_in.equals(compareType))
		{
			if (null != parameterValues && parameterValues.length > 0)
			{
				StringBuilder compareCondition = new StringBuilder();
				compareCondition.append(keyWord_in);
				compareCondition.append(" (");
				for (String parameterValue : parameterValues)
				{
					if (DicColumn.STR_DATATYPE_SET.contains(dicColumn
							.getDatatype()))
					{
						compareCondition.append("'");
						String nowDateStr = dealSysFunction(dicColumn
								.getDatatype(), parameterValue);
						compareCondition.append(nowDateStr);
						compareCondition.append("'");
					} else
					{
						compareCondition.append(parameterValue);
					}
					compareCondition.append(",");
				}
				compareCondition.deleteCharAt(compareCondition.length() - 1);
				compareCondition.append(")");
				result.add(compareCondition);
			}
		} else if (keyWord_equal.equals(compareType))
		{
			StringBuilder compareCondition = new StringBuilder();
			compareCondition.append(keyWord_equal);
			if (DicColumn.STR_DATATYPE_SET.contains(dicColumn.getDatatype()))
			{
				compareCondition.append("'");
				String nowDateStr = dealSysFunction(dicColumn.getDatatype(),
						parameterValues[0]);
				compareCondition.append(nowDateStr);
				compareCondition.append("'");
			} else
			{
				compareCondition.append(parameterValues[0]);
			}
			result.add(compareCondition);
		} else if (keyWord_like.equals(compareType))
		{
			StringBuilder compareCondition = new StringBuilder();
			compareCondition.append(keyWord_like);
			compareCondition.append("'%");
			String nowDateStr = dealSysFunction(dicColumn.getDatatype(),
					parameterValues[0]);
			compareCondition.append(nowDateStr);
			compareCondition.append("%'");
			result.add(compareCondition);
		} else
		{
			StringBuilder compareCondition = new StringBuilder();
			compareCondition.append(compareType);
			if (DicColumn.STR_DATATYPE_SET.contains(dicColumn.getDatatype()))
			{
				compareCondition.append("'");
				String nowDateStr = dealSysFunction(dicColumn.getDatatype(),
						parameterValues[0]);
				compareCondition.append(nowDateStr);
				compareCondition.append("'");
			} else
			{
				compareCondition.append(parameterValues[0]);
			}
			result.add(compareCondition);
		}

		return result;
	}

	
	
	private int getDateIndex(String value,String regex){
	       Pattern pattern = Pattern.compile(regex);
	        Matcher matcher = pattern.matcher(value.toLowerCase());
	            int idateindex=0;
	            if(matcher.find()){
	                //获取()内值
	                Pattern patternkh = Pattern.compile("\\([-]{0,1}\\d*\\)");
	                Matcher matcherkh = patternkh.matcher(value.toLowerCase());
	                if(matcherkh.find()){
	                    String str_kh=matcherkh.group(0);
	                    str_kh=str_kh.replace("(", "").replace(")", "");
	                    if("".equals(str_kh)){
	                        str_kh="0";
	                    }
	                    idateindex=Integer.parseInt(str_kh);
	                }
	            }
	    
	            return idateindex;
	}
	
	
	/*
	 * 当查询条件中录入值为"$sysdate()" 转换为当前系统时间字符串
	 */
	private String dealSysFunction(String dataType, String value) throws BusinessException
	{
		String dateFormatPtn = "yyyy-MM-dd";
		String timeFormatPtn = "yyyy-MM-dd HH:mm:ss";
	
		String regex="\\$sysdate\\([-]{0,1}\\d*\\)";
		Calendar c = Calendar.getInstance();
		c.setTime(new Date());   //设置当前日期
		    // Date
	        if (DicColumn.DATATYPE_DATE.equals(dataType))
	        {   
	           //大于当前系统时间 	          
	             c.add(Calendar.DATE, getDateIndex(value,regex)); //日期加1
                 SimpleDateFormat dateFormat = new SimpleDateFormat(dateFormatPtn);// 可以方便地修改日期格式
                 String nowDateStr = dateFormat.format(c.getTime());
	             return nowDateStr;
	            
	        } else if (DicColumn.DATATYPE_TIME.equals(dataType))
	        {
	            String nowDatetimeStr = DateUtil.getNowTime(timeFormatPtn);
	            SimpleDateFormat dateFormat = new SimpleDateFormat(dateFormatPtn);
	            int idateindex=getDateIndex(value,regex);
	            c.add(Calendar.DATE, idateindex);
	            if(idateindex>0){
	                 nowDatetimeStr = dateFormat.format(c.getTime());
	                nowDatetimeStr=nowDatetimeStr+" 00:00:00";
	                }else if(idateindex<0){
	                    nowDatetimeStr = dateFormat.format(c.getTime());
	                    nowDatetimeStr=nowDatetimeStr+" 23:59:59";  
	                }
	            
	                return nowDatetimeStr;
	        }			
		
		return value;
	}

	private static Set<String> equalXtypeSet = new HashSet<String>();
	static
	{
		equalXtypeSet.add(ViewItem.XTYPE_1);
		equalXtypeSet.add(ViewItem.XTYPE_7);
		equalXtypeSet.add(ViewItem.XTYPE_8);
	}

	private String ConstructConditionAssociateTable(String columncode,
			String associateTableCode, String associateColumncode,
			String alias, long aliasNumber)
	{
		String thisAlias = ROOTALIAS + aliasNumber;

		StringBuilder result = new StringBuilder(" and exists( select * from ");
		result.append(associateTableCode);
		result.append(" ");
		result.append(thisAlias);
		result.append(" where ");
		result.append(thisAlias);
		result.append(".");
		result.append(associateColumncode);
		result.append(" = ");
		result.append(alias);
		result.append(".");
		result.append(columncode);
		result.append(" ");
		return result.toString();
	}

	/**
	 * Description : 判断是否是主键字段
	 * 
	 * @param dicColumn
	 * @return
	 * @throws BusinessException
	 * 
	 */
	private boolean isKeyColumn(DicColumn dicColumn) throws BusinessException
	{
		boolean result = false;

		if (dicColumn != null)
		{
			String columncode = dicColumn.getDicColumnPK().getColumncode();
			String tableCode = dicColumn.getDicColumnPK().getTablecode();

			DicTable dicTable = dicTableService.getDicTable(tableCode);
			if (dicTable != null)
			{
				if (columncode.equals(dicTable.getKeycolumns()))
				{
					result = true;
				}
			}
		}

		return result;
	}

	/**
	 * Description : 产生sql排序串
	 * 
	 * @param targetPanel
	 * @param alias
	 * @return
	 * @throws BusinessException
	 * 
	 */
	public String ConstructOrderBySQL(ViewPanel targetPanel)
			throws BusinessException
	{
		logger.info("ConstructOrderBySQL method run begin.");
		String tablecode = targetPanel.getTablecode();
		LinkedHashMap<String, String> targetColumncodes = new LinkedHashMap<String, String>();
		List<ViewItem> viewItemsList = viewItemDaoImpl
				.findItemByViewPanelid(targetPanel.getId());
		for (ViewItem viewItem : viewItemsList)
		{
			if (viewItem.getOrderFlag())
			{
				String orderSc = ORDER_DESC;
				if (ViewItem.ORDER_SC_ASC.equals(viewItem.getOrderSc()))
				{
					orderSc = ORDER_ASC;
				}
				targetColumncodes.put(viewItem.getFirstColumncode(), orderSc);
			}
		}

		String result = ConstructOrderBySQL(tablecode, targetColumncodes);
		logger.info("ConstructOrderBySQL method run end.");
		return result;
	}

	/**
	 * Description : 产生sql排序串
	 * 
	 * @param tablecode
	 * @param orderColumncodes
	 * @return
	 * @throws BusinessException
	 * 
	 */
	private String ConstructOrderBySQL(String tablecode,
			LinkedHashMap<String, String> orderColumncodes)
			throws BusinessException
	{
		long aliasNumber = 0;
		String alias = ROOTALIAS + aliasNumber;

		StringBuilder result = new StringBuilder(" order by ");
		Set<String> orderColumncodeSet = null;
		if (null != orderColumncodes)
		{
			orderColumncodeSet = orderColumncodes.keySet();
		}

		if (orderColumncodeSet != null && orderColumncodeSet.size() > 0)
		{
			for (Iterator<String> iterator = orderColumncodeSet.iterator(); iterator
					.hasNext();)
			{
				String columncode = iterator.next();
				int pos = columncode.indexOf(ViewConstant.COLUMN_SEQ);
				if (pos != -1)
				{
					columncode = columncode.substring(0, pos);
				}
				result.append(alias);
				result.append(".");
				result.append(columncode);
				result.append(" ");
				String orderTypes = orderColumncodes.get(columncode);
				if (!ViewUtil.isNuLLStr(orderTypes)
						&& ORDER_ASC.equals(orderTypes))
				{
					result.append(ORDER_ASC);
				} else
				{
					result.append(ORDER_DESC);
				}
				result.append(",");
			}
		} else
		{
			DicTable dicTable = dicTableService.getDicTable(tablecode);

			String ordercolumncode = dicTable.getOrdercolumn();
			if (ordercolumncode != null && ordercolumncode.length() > 0)
			{
				result.append(alias);
				result.append(".");
				result.append(ordercolumncode);
				result.append(" ");
				result.append(ORDER_ASC);
				result.append(",");
			}

			List<String> keycolumns = dicTable.getKeycolumnlist();
			for (String keycolumn : keycolumns)
			{
				result.append(alias);
				result.append(".");
				result.append(keycolumn);
				result.append(" ");
				result.append(ORDER_DESC);
				result.append(",");
			}
		}

		if (" order by ".equals(result.toString()))
		{
			return "";
		}

		if (',' == result.charAt(result.length() - 1))
		{
			result.deleteCharAt(result.length() - 1);
		}

		return result.toString();
	}

	private List<ViewItem> orderViewItemsList(List<ViewItem> viewItemsList)
	{
		List<ViewItem> orderViewItemsList = new ArrayList<ViewItem>();
		List<ViewItem> multilevelViewItemsList = new ArrayList<ViewItem>();

		for (ViewItem viewItem : viewItemsList)
		{
			if (!viewItem.isMultilevelByName())
			{
				orderViewItemsList.add(viewItem);
			} else
			{
				multilevelViewItemsList.add(viewItem);
			}
		}

		orderViewItemsList.addAll(multilevelViewItemsList);

		return orderViewItemsList;
	}

	public static void main(String[] args)
	{
	/*	String columncode = "visible,value";
		int pos = columncode.indexOf(ViewConstant.COLUMN_SEQ);
		if (pos != -1)
		{
			columncode = columncode.substring(0, pos);
		}
		System.out.println(columncode);*/
	    
	/*    String regex="\\$sysdate\\([-]{0,1}\\d*\\)";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher("$sysdate(-1)");
       
        Pattern pattern2 = Pattern.compile("\\([-]{0,1}\\d*\\)");
        Matcher matcher2 = pattern2.matcher("$sysdate()");
        if(matcher2.find()){
            String str=matcher2.group(0);
            str=str.replace("(", "").replace(")", "");
            if("".equals(str)){
                str="0";
            }
            System.out.println(str);
            Integer.parseInt(str);
           }
  */
	    
 /*      try {
        System.out.println(new SQLConstructServieImpl().dealSysFunction("T","$sysdate(p)"));
    } catch (BusinessException e) {
       
        e.printStackTrace();
    }
        */
        
        
	}
}
